﻿Imports System.Data
Imports System.Data.SqlClient
Imports Datos.Acceso_Datos
Public Class Acceso_Datos
    Inherits Transaccion
    Public Function Ejecutar_Scalar(ByVal Nombre_Procedimiento As String, ByVal ParamArray args As Object()) As Object
        'valor_mensaje = Nothing
        'msj = Nothing
        Dim rows As Object
        Try
            Dim cmd As SqlCommand
            Abrir_Conexion()
            Iniciar_Transaccion()
            cmd = New SqlCommand(Nombre_Procedimiento, con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Transaction = MTransaccion
            SqlCommandBuilder.DeriveParameters(cmd)
            'cargar parametros
            Cargar_Parametros(cmd, args)
            rows = cmd.ExecuteScalar
        Catch ex As Exception
            Abortar_Transaccion()
            Throw ex
        Finally
            Terminar_Transaccion()
            Cerrar_Conexion()
        End Try
        Return rows
    End Function
    Public Function Ejecutar(ByVal Nombre_Procedimiento As String, ByVal ParamArray args As Object()) As Int32
        'valor_mensaje = Nothing
        'msj = Nothing
        Dim rows As Integer = 0
        Try

            Dim cmd As SqlCommand

            Abrir_Conexion()
            Iniciar_Transaccion()

            cmd = New SqlCommand(Nombre_Procedimiento, con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Transaction = MTransaccion

            SqlCommandBuilder.DeriveParameters(cmd)

            'cargar parametros
            Cargar_Parametros(cmd, args)

            rows = cmd.ExecuteNonQuery

            'For Each p As IDbDataParameter In cmd.Parameters
            '    If (p.Direction = ParameterDirection.InputOutput And p.Direction = ParameterDirection.Output) Then

            '        If (valor_mensaje Is Nothing) Then
            '            valor_mensaje = p.Value
            '        Else
            '            msj = p.Value
            '        End If

            '    End If
            'Next

        Catch ex As Exception
            Abortar_Transaccion()
            Throw ex
        Finally
            Terminar_Transaccion()
            Cerrar_Conexion()
        End Try
        Return rows
    End Function
    Private Sub Cargar_Parametros(ByVal cmd As SqlCommand, ByVal ParamArray args As Object())
        Try
            For i = 1 To cmd.Parameters.Count - 1
                Dim p As SqlParameter = cmd.Parameters(i)
                If (p.Value = i <= args.Length) Then
                    p.Value = args(i - 1)
                Else
                    p.Value = Nothing
                End If
            Next
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Public Function Consultar(ByVal Nombre_Procedimiento As String, ByVal ParamArray args As Object()) As DataSet

        Dim da As SqlDataAdapter
        Dim dts As New DataSet

        Try
            Abrir_Conexion()
            Iniciar_Transaccion()
            da = New SqlDataAdapter(Nombre_Procedimiento, con)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Transaction = MTransaccion
            SqlCommandBuilder.DeriveParameters(da.SelectCommand)
            Cargar_Parametros(da.SelectCommand, args)
            da.Fill(dts)
        Catch ex As Exception
            Abortar_Transaccion()
            Throw ex
        Finally
            Terminar_Transaccion()
            Cerrar_Conexion()
        End Try
        Return dts
    End Function
    Public Function ConsultarDT(ByVal Nombre_Procedimiento As String, ByVal ParamArray args As Object()) As DataTable

        Dim da As SqlDataAdapter
        Dim dts As New DataTable

        Try
            Abrir_Conexion()
            Iniciar_Transaccion()
            da = New SqlDataAdapter(Nombre_Procedimiento, con)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Transaction = MTransaccion
            SqlCommandBuilder.DeriveParameters(da.SelectCommand)
            Cargar_Parametros(da.SelectCommand, args)
            da.Fill(dts)
        Catch ex As Exception
            Abortar_Transaccion()
            Throw ex
        Finally
            Terminar_Transaccion()
            Cerrar_Conexion()
        End Try
        Return dts
    End Function
End Class
